<?php
/**
 * Description of Usersclass
 *
 * @author Jeffy_shih
 * @copyright DoRISE team, Science Education center, NTNU.
 * @since 2010-02-08
 * @version 1.0
 */

class Subject {

    private $mysqli;

    function  __construct() {
        //引入資料庫連結參數設定
        include '../inc/oaConn.inc.php';
        $this->mysqli = @new mysqli($mysqlHost,$mysqlUser,$mysqlPassword,$mysqlDB);
        if(mysqli_connect_errno()) {
            throw new Exception('無法建立資料庫連線！'.mysqli_connect_error());
            $this->mysqli = false;
            exit();
        }

        //連線使用編碼
        $this->mysqli->query("SET NAMES 'utf8'");

    }

    /**
     *
     * @param Integer $intSubjectId
     * @return Array
     */
    public function getSingleSubject($intSubjectId){
        //檢查學科編號是否為空白
        if (empty ($intSubjectId)) {
            throw new Exception('缺少學科編號！');
        }
        
        $sql_selSubject = 'SELECT * FROM `subjects` WHERE `id`=' . $intSubjectId;
        $rs_selSubject = $this->mysqli->query($sql_selSubject);

        if(!$rs_selSubject){
            throw new Exception('無法查詢單一學科資料！');
        }else{
            if($rs_selSubject->num_rows == 0){
                throw new Exception('無指定編號的學科資料！');
            }
            $row_selSubject = $rs_selSubject->fetch_row();
            return $row_selSubject;
        }
    }

    /**
     *
     * @return Array
     */
    public function getSubjectList() {
        
        $arySubjects = array();
        
        $sql_getSubjectList = 'SELECT * FROM `subjects`';
        $rs_getSubjectList = $this->mysqli->query($sql_getSubjectList);

        if(!$rs_getSubjectList){
            throw new Exception('無法查詢學科資料！');
        }else{
            
            while($row_getSubjectList = $rs_getSubjectList->fetch_row()){
                array_push($arySubjects, $row_getSubjectList);
            }

            //釋放查詢結果
            $rs_getSubjectList->free_result();
        }

        return $arySubjects;
    }

    /**
     *
     * @param String $strEngName
     * @param String $strChtName
     * @return Boolean
     */
    public function addSubject($strEngName, $strChtName) {
        $bolRtn = false;
        //先查詢是否已有相同名稱
        if(empty ($strEngName) || empty($strChtName)){
            throw new Exception('學科名稱不可為空白！');
        }

        $sql_checkDuplicateValue = 'SELECT * FROM `subjects` WHERE `name`=\''.$strEngName.'\' OR `name_chinese`=\''.$strChtName.'\'';
        $rs_checkDuplicateValue = $this->mysqli->query($sql_checkDuplicateValue);
        
        //檢查是否已有重複值
        if(!$rs_checkDuplicateValue){
            throw new Exception('查詢是否有重複時發生錯誤！');
        }else{
            if($rs_checkDuplicateValue->num_rows != 0){
                throw new Exception('已有相同的學科名稱存在！');
            }else{
                $strNow = $this->getNowTime();
                $sql_addSubject = 'INSERT INTO `subjects` SET `name`=\''.$strEngName.'\', `name_chinese`=\''.$strChtName.'\', `created_date`=\''.$strNow.'\'';
                $rs_addSubject = $this->mysqli->query($sql_addSubject);
                if(!$rs_addSubject){
                    throw new Exception('新增學科資料時發生錯誤！');
                }else{
                    $bolRtn = true;
                }
            }
        }

        return $bolRtn;
    }

    /**
     *
     * @param Integer $intSubjectId
     * @param String $strEngName
     * @param String $strChtName
     * @return Boolean
     */
    public function editSubject($intSubjectId, $strEngName, $strChtName) {
        $bolRtn = false;
        //檢查是否有學科編號
        if(empty ($intSubjectId)){
            throw new Exception('缺少學科編號！');
        }

        //檢查學科名稱是否為空白
        if(empty ($strEngName) || empty ($strChtName)){
            throw new Exception('學科名稱不可為空白！');
        }

        //更新學科資料
        $strNow = $this->getNowTime();
        $sql_uptSubject = 'UPDATE `subjects` SET `name`=\''.$strEngName.'\', `name_chinese`=\''.$strChtName.'\', `modified_date`=\''.$strNow.'\' WHERE `id`='.$intSubjectId;
        if(!$this->mysqli->query($sql_uptSubject)){
            throw new Exception('更新學科資料時發生錯誤！');
        }else{
            $bolRtn = true;
        }

        return $bolRtn;
    }

    /**
     *
     * @param Integer $intSubjectId
     * @return Boolean
     */
    public function delSubject($intSubjectId) {
        $bolRtn = false;
        //檢查學科編號是否為空白
        if (empty ($intSubjectId)) {
            throw new Exception('缺少學科編號！');
        }
        //檢查學科是否為使用中，使用中不允許刪除
        $sql_chkUsed = 'SELECT * FROM `questionpool_question` WHERE `subject`=' . $intSubjectId;
        $rs_chkUsed = $this->mysqli->query($sql_chkUsed);
        if(!$rs_chkUsed){
            throw new Exception('異常！查詢學科是否為使用中時發生錯誤。');
        }else{
            if($rs_chkUsed->num_rows > 0){
                throw new Exception('學科項目正在使用中，不允許刪除。');
            }else{
                //非使用中的學科，先刪除該學科下主題在刪除學科本身
                $sql_delTopic = 'DELETE FROM `topics` WHERE `subject_id`=' . $intSubjectId;
                if(!$this->mysqli->query($sql_delTopic)){
                    throw new Exception('錯誤！刪除學科下的主題時發生錯誤。');
                }else{
                    //刪除學科下主題後再刪除該學科
                    $sql_delSubject ='DELETE FROM `subjects` WHERE `id`=' . $intSubjectId;
                    if(!$this->mysqli->query($sql_delSubject)){
                        throw new Exception('錯誤！已刪除學科下主題，於刪除學科時發生錯誤。');
                    }else{
                        $bolRtn = true;
                    }
                }
            }
        }
        
        return $bolRtn;
    }

    /**
     *
     * @return String
     */
    private function getNowTime() {
        return date('Y-m-d H:i:s');
    }

    /**
     *
     * @param Integer $intSubjectId
     * @return Integer
     */
    public function getTopicTotalNumById($intSubjectId){
        $intTopicCountNum = 0;
        
        if (empty ($intSubjectId)) {
            throw new Exception('缺少學科編號！');
        }

        $sql_getTopicNum = 'SELECT COUNT(*) FROM `topics` WHERE `subject_id`=' . $intSubjectId;
        $rs_getTopicNum = $this->mysqli->query($sql_getTopicNum);
        if(!$rs_getTopicNum){
            throw new Exception('查詢學科下主題數時發生錯誤！');
        }else{
            $row_getTopicNum = $rs_getTopicNum->fetch_row();
            $intTopicCountNum = $row_getTopicNum[0];
        }

        return $intTopicCountNum;
    }

    /**
     *
     * @param Integer $intSubjectId
     * @return String
     */
    public function getSubjectNameBySId($intSubjectId){
        if(empty ($intSubjectId)){
            throw new Exception('缺少必要的學科編號！');
        }

        $sql_getSubjectName = 'SELECT `name`, `name_chinese` FROM `subjects` WHERE `id`=' . $intSubjectId;
        $rs_getSubjectName = $this->mysqli->query($sql_getSubjectName);
        if(!$rs_getSubjectName){
            throw new Exception('查詢學科名稱時發生錯誤！');
        }else{
            $row_getSubjectName = $rs_getSubjectName->fetch_row();
            return $row_getSubjectName;
        }
    }

    function  __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->mysqli = FALSE;
        }
    }
}
?>
